package com.ztc.AppUser.dao;

import com.aqgj.util.BaseDaoImpl;
import com.aqgj.util.Pager;
import com.ztc.AppUser.bean.AppUser;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;


@Repository("AppUserDao")
public class AppUserDao extends BaseDaoImpl<AppUser> {

    /**
     * 根据类别获取用户列表.
     * @param pageNo    页 码
     * @param pageSize 每页数量
     * @param search    搜索条件
     * @param distinguishType  类别（1 除待审核数据，2待审核数据）
     * @param isExpress  类别（0 顺风车，1快递员）
     * @return
     */
    public Pager<List<Map>> getPrivateCarUserListByType(Integer pageNo,Integer pageSize,String search,Integer distinguishType,Integer isExpress,String cid){
        Pager<List<Map>> pager=new Pager<>();
        pager.setCurrentPage(pageNo);
        pager.setPageSize(pageSize);
        StringBuffer sql=new StringBuffer("select a.id,a.name,a.real_name realName,a.sex,a.birthday,a.user_card,a.phone,a.`status`,a.create_time,a.certificate_end_time certificateEndTime,a.`type`,s.FULL_NAME inArea,a.exp_status expStatus,a.private_car_status privateCarStatus from app_user a LEFT JOIN sys_area s ON a.in_area=s.CODE  where 1=1 ");
        if(distinguishType==1){
            if(isExpress==1){
                sql.append(" and a.transport_id='"+cid+"' and a.type in(3,4) and a.exp_status <> 1 ");
            }else{
                sql.append(" and a.type in(2,4) and a.private_car_status <> 1 ");
            }
        }else{
            if(isExpress==0){
                sql.append(" and a.private_car_status = 1 ");
            }else{
                sql.append(" and a.transport_id='"+cid+"' and a.exp_status=1 ");
            }
        }
        if(search!=null&&!"".equals(search.trim())){
            sql.append(" and (a.real_name like '%"+search.trim()+"%' or a.user_card like '%"+search.trim()+"%' or a.phone like '%"+search.trim()+"%' ");
            search= sexReversal(search);
            sql.append(" or a.sex like '%"+search+"%') ");
        }
        pager.setRecordTotal(this.currentSession().createNativeQuery(sql.toString()).getResultList().size());
        sql.append("  limit ?,? ");
        pager.setContent(this.currentSession().createNativeQuery(sql.toString()).setParameter(1,pager.getStart()).setParameter(2,pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList());
        return pager;
    }


    public Map getUserInfo(String uid) {
        String sql= "SELECT a.id uid,a.name,a.user_card card,a.certificate_end_time certificate,a.examine_remark remake,a.birthday,IFNULL(a.real_name,'') realName,a.sex,a.user_card userCard,a.phone,a.`type`,a.`status`,a.exp_status expStatus,a.private_car_status privateCarStatus," +
                    "IFNULL(s.FULL_NAME,'') inArea,a.create_time createTime,a.examine_time examineTime,c.id cid,c.brand,c.car_number carNumber,c.color,c.property,c.car_type carType,c.seat_num seatNum " +
                    "FROM app_user a " +
                    "LEFT JOIN ztc_car c ON a.id=c.transport_id AND c.`type`=2 " +
                    "LEFT JOIN sys_area s ON a.in_area=s.CODE " +
                    "WHERE a.id='"+uid+"'";
        return (Map)this.currentSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    public void save(AppUser appUser) {
        if(appUser!=null){
          this.currentSession().merge(appUser);
        }else{
          this.currentSession().save(appUser);
        }
    }

    /**
     * 获取App用户列表.
     * @param pageNo    页 码
     * @param pageSize 每页数量
     * @param search    搜索条件
     * @return
     */
    public Pager<List<Map>> getAppUserList(Integer pageNo, Integer pageSize, String search) {
        Pager<List<Map>> pager=new Pager<>();
        pager.setCurrentPage(pageNo);
        pager.setPageSize(pageSize);
        StringBuffer sql=new StringBuffer("select a.id,a.name,a.real_name,a.sex,a.birthday,a.user_card,a.phone,a.`status`,a.create_time,a.certificate_end_time,a.`type`,s.FULL_NAME inArea from app_user a LEFT JOIN sys_area s ON a.in_area=s.CODE  where 1=1 ");
        if(search!=null&&!"".equals(search.trim())){
            sql.append(" and (a.real_name like '%"+search.trim()+"%' or a.phone like '%"+search.trim()+"%' ");
            search= sexReversal(search);
            sql.append(" or a.sex like '%"+search+"%') ");
        }
        pager.setRecordTotal(this.currentSession().createNativeQuery(sql.toString()).getResultList().size());
        sql.append(" order by a.create_time desc limit ?,? ");
        pager.setContent(this.currentSession().createNativeQuery(sql.toString()).setParameter(1,pager.getStart()).setParameter(2,pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList());
        return pager;
    }

    private String sexReversal(String search){
        switch (search.trim()){
            case "男":search="1";break;
            case "女":search="2";break;
            default:break;
        }
        return search;
    }

    //获取所有驾驶证证件到期且未被冻结的司机
    public List<AppUser> getExpireAppUserList() {
        String sql ="select * from app_user z where z.`status`=1 and z.`type` in(2,4) and z.private_car_status=3 and z.certificate_end_time <curdate()" ;
        return this.currentSession().createNativeQuery(sql, AppUser.class).getResultList();
    }
}